﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Liger.Data;
using Liger.Model;

namespace Test
{
    public class NorthwindQueryTest : BaseTest
    {
        public NorthwindQueryTest(DbContext db)
            : base(db)
        {
        }

        /// <summary>
        /// 测试 Distinct
        /// </summary>
        public void TestDistinct()
        {
            var result = Db.From<Orders>()
                .Distinct()
                .Select(Orders._.CustomerID)
                .ToDataTable();

            this.AssertReturn(result != null);
        }

        /// <summary>
        /// 测试 Two Order by
        /// </summary>
        public void TestTwoOrderBy()
        {
            var result = Db.From<Orders>()
                .OrderBy(Orders._.OrderDate.Desc, Orders._.CustomerID.Desc)
                .ToFirst();

            this.AssertReturn(result != null);
        }


        /// <summary>
        /// 测试 Group by
        /// </summary>
        public void TestGroupBy()
        {
            var result = Db.From<Orders>()
                .Select(Orders._.Freight.Sum(),Orders._.OrderID.Count(), Orders._.CustomerID)
                .GroupBy(Orders._.CustomerID)
                .ToDataTable();

            this.AssertReturn(result !=null && result.Rows.Count >0);
        }

        /// <summary>
        /// 测试 Group by
        /// </summary>
        public void TestGroupBy2()
        {
            var result = Db.From<Orders>()
                .Select(Orders._.Freight.Sum())
                .GroupBy(Orders._.CustomerID)
                .ToScalar<decimal>();

            this.AssertReturn(result > 0);
        }

        /// <summary>
        /// 测试 Group by (多个字段)
        /// </summary>
        public void TestGroupByMulField()
        {
            var result = Db.From<Orders>()
                .Select(Orders._.Freight.Sum(), Orders._.Freight.Count().As("count"), Orders._.EmployeeID, Orders._.CustomerID)
                .GroupBy(Orders._.CustomerID,Orders._.EmployeeID)
                .ToDataTable();

            this.AssertReturn(result != null && result.Rows.Count > 0);
        }

        /// <summary>
        /// 测试 Group by Having
        /// </summary>
        public void TestGroupByHavingSum()
        {
            var result = Db.From<Orders>()
                .Select(Orders._.Freight.Sum())
                .GroupBy(Orders._.CustomerID)
                .Having(Orders._.Freight.Sum() > 250)
                .ToScalar<decimal>();

            this.AssertReturn(result > 250);
        }


        /// <summary>
        /// 测试 分页
        /// </summary>
        public void TestPaging()
        {
            var result = Db.From<Orders>() 
                .Page(20,2)
                .ToList();

            this.AssertReturn(result.Count == 20);
        }

        /// <summary>
        /// 测试 多字段排序
        /// </summary>
        public void TestPagingWithMulOrderby()
        {
            var result = Db.From<Orders>()
                .Select(Orders._.OrderID, Orders._.OrderDate, Orders._.EmployeeID, Orders._.CustomerID)
                .Where(Orders._.CustomerID.Like("A"))
                .OrderBy(Orders._.EmployeeID.Desc, Orders._.CustomerID.Asc)
                .Page(20, 2)
                .ToList();

            this.AssertReturn(result.Count == 20);
        }

        /// <summary>
        /// 测试 分页 带条件
        /// </summary>
        public void TestPagingWithWhere()
        {
            var result = Db.From<Orders>()
                .Select(Orders._.OrderID, Orders._.OrderDate, Orders._.EmployeeID, Orders._.CustomerID)
                .Where(Orders._.CustomerID.Like("A"))
                .OrderBy(Orders._.EmployeeID.Desc, Orders._.CustomerID.Asc)
                .Page(20, 2)
                .ToList();

            this.AssertReturn(result.Count == 20);
        }



        /// <summary>
        /// 测试 TestJOIN
        /// </summary>
        public void TestJOIN()
        {
            var result = Db.From<Orders>()
                .Select(
                Orders._.OrderID,
                Customers._.CustomerID,
                Customers._.ContactName,
                Customers._.CompanyName
                )
                .Join<Customers>(Orders._.CustomerID == Customers._.CustomerID)
                .ToDataTable();

            this.AssertReturn(result != null && result.Rows.Count > 0);
        }


        /// <summary>
        /// 测试 TestLeftJOIN
        /// </summary>
        public void TestLeftJOIN()
        {
            var result = Db.From<Orders>()
                .Select(
                Orders._.OrderID,
                Customers._.CustomerID,
                Customers._.ContactName,
                Customers._.CompanyName
                )
                .LeftJoin<Customers>(Orders._.CustomerID == Customers._.CustomerID)
                .ToDataTable();

            this.AssertReturn(result != null && result.Rows.Count > 0);
        }




        /// <summary>
        /// 测试 TestRightJOIN
        /// </summary>
        public void TestRightJOIN()
        {
            var result = Db.From<Orders>()
                .Select(
                Orders._.OrderID,
                Customers._.CustomerID,
                Customers._.ContactName,
                Customers._.CompanyName
                )
                .RightJoin<Customers>(Orders._.CustomerID == Customers._.CustomerID) 
                .ToDataTable();

            this.AssertReturn(result != null && result.Rows.Count > 0);
        }


        /// <summary>
        /// 测试 JOIN 带条件
        /// </summary>
        public void TestJOINWithWhere()
        {
            var result = Db.From<Orders>()
                .Select(
                Orders._.OrderID,
                Customers._.CustomerID,
                Customers._.ContactName,
                Customers._.CompanyName
                )
                .InnerJoin<Customers>(Orders._.CustomerID == Customers._.CustomerID
                && Customers._.ContactName == "Mary Saveley")
                .ToDataTable();

            this.AssertReturn(result != null && result.Rows.Count > 0);
        }


        /// <summary>
        /// 测试子查询 In
        /// </summary>
        public void TestSubwhereIn()
        {
            var result = Db.From<Orders>()
                .Where(Orders._.CustomerID.SelectIn(
                Db.From<Customers>().Where(Customers._.ContactName == "ALFKI").Select(Customers._.CustomerID).Distinct()
                ))
                .ToDataTable(); 

            this.AssertReturn(result != null);
        }


        /// <summary>
        /// 测试子查询 Not in
        /// </summary>
        public void TestSubwhereNotIn()
        {
            var result = Db.From<Orders>()
                .Where(Orders._.CustomerID.SelectNotIn(
                Db.From<Customers>().Where(Customers._.ContactName == "ALFKI").Select(Customers._.CustomerID).Distinct()
                ))
                .ToDataTable();

            this.AssertReturn(result != null);
        }


        /// <summary>
        /// 测试子查询 等于
        /// </summary>
        public void TestSubwhereEqual()
        {
            var result = Db.From<Orders>()
                .Where(Orders._.CustomerID == 
                Db.From<Customers>().Where(Customers._.ContactName == "ALFKI").Select(Customers._.CustomerID).Distinct()
                )
                .ToDataTable();

            this.AssertReturn(result != null);
        }



        /// <summary>
        /// 测试子查询 不等于
        /// </summary>
        public void TestSubwhereNotEqual()
        {
            var result = Db.From<Orders>()
                .Where(Orders._.CustomerID !=
                Db.From<Customers>().Where(Customers._.ContactName == "ALFKI").Select(Customers._.CustomerID).Distinct()
                )
                .ToDataTable();

            this.AssertReturn(result != null);
        }
 
    }
}
